﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using School.Entities;

namespace School.Service.BOOKS
{
    public class SRV_BOOK_ISSUE
    {
        private static SchoolEntities db = new SchoolEntities();
        public static IEnumerable<object> VoucherSearch(long voucherNum, DateTime startdate)
        {

            try
            {
                var query = from payOrder in db.SRV_BOOK_PAYMENT_ITEMS
                            where payOrder.Payment_Order_Number ==
                            (from voucher in db.ACC_Voucher
                             where voucher.Number == voucherNum && voucher.Date == startdate&&
                                 voucher.ORG_EductionalYear.IsCurrent == true
                             select voucher).FirstOrDefault().Payment_Order_Number
                            //            voucher.Date == date && 
                            //            voucher.ORG_EductionalYear.IsCurrent == true
                            select new
                            {
                                Number = voucherNum,
                                Date = (from voucher in db.ACC_Voucher where voucher.Payment_Order_Number == payOrder.Payment_Order_Number select voucher).FirstOrDefault().Date,
                                BookId = payOrder.Book_Id,
                                BookName = payOrder.SRV_Book.Name,
                                StudentId = (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().Student_Id,
                                StudentName = (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().TE_Student.Ar_FirstName + " " + (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().TE_Student.Ar_SecondName + " " + (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().TE_Student.Ar_ThirdNmae + " " + (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().TE_Student.Ar_FourthName,
                                VoucherValue = payOrder.BookQty,
                                Paymnet_Order_Number = payOrder.Payment_Order_Number,
                                ExpenseId = (from voucher in db.ACC_Voucher where voucher.Payment_Order_Number == payOrder.Payment_Order_Number select voucher).FirstOrDefault().Expense_Id,
                                IsIssue = !(db.SRV_BOOK_TRANSACTION.Where(s => s.Payment_Order_Number == payOrder.Payment_Order_Number && s.Book_Id == payOrder.Book_Id).FirstOrDefault().Payment_Order_Number.HasValue)
                            };
                return query.ToList();
            }
            catch
            {
                return null;
            }

        }
        public static IEnumerable<object> VoucherSearch(long voucherNum)
        {
            try
            {
                var query = from payOrder in db.SRV_BOOK_PAYMENT_ITEMS
                            where payOrder.Payment_Order_Number ==
                            (from voucher in db.ACC_Voucher
                             where voucher.Number == voucherNum &&
                                 voucher.ORG_EductionalYear.IsCurrent == true
                             select voucher).FirstOrDefault().Payment_Order_Number
                            //            voucher.Date == date && 
                            //            voucher.ORG_EductionalYear.IsCurrent == true
                            select new
                            {
                                Number = voucherNum,
                                Date = (from voucher in db.ACC_Voucher where voucher.Payment_Order_Number == payOrder.Payment_Order_Number select voucher).FirstOrDefault().Date,
                                BookId = payOrder.Book_Id,
                                BookName = payOrder.SRV_Book.Name,
                                StudentId = (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().Student_Id,
                                StudentName = (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().TE_Student.Ar_FirstName + " " + (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().TE_Student.Ar_SecondName + " " + (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().TE_Student.Ar_ThirdNmae + " " + (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().TE_Student.Ar_FourthName,
                                VoucherValue = payOrder.BookQty,
                                Paymnet_Order_Number = payOrder.Payment_Order_Number,
                                ExpenseId = (from voucher in db.ACC_Voucher where voucher.Payment_Order_Number == payOrder.Payment_Order_Number select voucher).FirstOrDefault().Expense_Id,
                                IsIssue = !(db.SRV_BOOK_TRANSACTION.Where(s => s.Payment_Order_Number == payOrder.Payment_Order_Number && s.Book_Id == payOrder.Book_Id).FirstOrDefault().Payment_Order_Number.HasValue)
                            };
                return query.ToList();
            }
            catch
            {
                return null;
            }

        }
        public static IEnumerable<object> VoucherSearch(DateTime startdate)
        {
            try
            {
                var query = from payOrder in db.SRV_BOOK_PAYMENT_ITEMS
                            where payOrder.Payment_Order_Number ==
                            (from voucher in db.ACC_Voucher
                             where voucher.Date == startdate &&
                                 voucher.ORG_EductionalYear.IsCurrent == true
                             select voucher).FirstOrDefault().Payment_Order_Number
                            //            voucher.Date == date && 
                            //            voucher.ORG_EductionalYear.IsCurrent == true
                            select new
                            {
                                Number = (from voucher in db.ACC_Voucher where voucher.Payment_Order_Number == payOrder.Payment_Order_Number select voucher).FirstOrDefault().Number,
                                Date = (from voucher in db.ACC_Voucher where voucher.Payment_Order_Number == payOrder.Payment_Order_Number select voucher).FirstOrDefault().Date,
                                BookId = payOrder.Book_Id,
                                BookName = payOrder.SRV_Book.Name,
                                StudentId = (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().Student_Id,
                                StudentName = (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().TE_Student.Ar_FirstName + " " + (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().TE_Student.Ar_SecondName + " " + (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().TE_Student.Ar_ThirdNmae + " " + (from payordera in db.SRV_BOOK_Payment_Order where payordera.Order_Id == payOrder.Order_Id select payordera).FirstOrDefault().TE_Student.Ar_FourthName,
                                VoucherValue = payOrder.BookQty,
                                Paymnet_Order_Number = payOrder.Payment_Order_Number,
                                ExpenseId = (from voucher in db.ACC_Voucher where voucher.Payment_Order_Number == payOrder.Payment_Order_Number select voucher).FirstOrDefault().Expense_Id,
                                IsIssue = !(db.SRV_BOOK_TRANSACTION.Where(s => s.Payment_Order_Number == payOrder.Payment_Order_Number && s.Book_Id == payOrder.Book_Id).FirstOrDefault().Payment_Order_Number.HasValue)
                            };
                return query.ToList();
            }
            catch
            {
                return null;
            }

        }
       
    }
}
